﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

using ZhimeiSoft.Framework.Server.Base;
using ZhimeiSoft.CharmManager.DOL;
using ZhimeiSoft.CharmManager.Server.DAL.Interface;

namespace ZhimeiSoft.CharmManager.Server.DAL
{
    public class CustomerServiceItemDAL : BaseDal, ICustomerServiceItemDAL
    {
        /// <summary>
        /// 设置SQL语句中的参数
        /// </summary>
        /// <param name="cmd">DbCommand对象</param>CompleteCustomServiceItem
        /// <param name="customer">客户实体类</param>
        private void AddInSqlParameters(ref DbCommand cmd, DBEntityCustomerServiceItem csi)
        {
            if (csi == null || cmd == null)
                return;

            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CBUYTIME, DbType.DateTime, csi.CBUYTIME);
            //DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CLASTSERVICETIME, DbType.DateTime, csi.CLASTSERVICETIME);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CMEMO, DbType.String, csi.CMEMO);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.COUNT, DbType.Int32, csi.COUNT);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CREMAINCOUNT, DbType.Int32, csi.CREMAINCOUNT);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CSERVICEENDTIME, DbType.DateTime, csi.CSERVICEENDTIME);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CSERVICESTARTTIME, DbType.DateTime, csi.CSERVICESTARTTIME);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CSTATUS, DbType.String, csi.CSTATUS);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERID, DbType.Int32, csi.CUSTOMERID);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.SERVICEITEMID, DbType.Int32, csi.SERVICEITEMID);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CTOTALNEEDPAY, DbType.Decimal, csi.CTOTALNEEDPAY);
        }

        public void InsertCustomerServiceItem(DBEntityCustomerServiceItem csi)
        {
            string strSQL = CustomerServiceItemDAL_Const.SQL_INSERT_CUSTOMERSERVICEITEM;
            DbCommand cmd = DB.GetSqlStringCommand(strSQL);
            AddInSqlParameters(ref cmd, csi);
            DB.ExecuteNonQuery(cmd);
        }

        public void UpdateCustomerServiceItem(DBEntityCustomerServiceItem csi)
        {
            string strSQL = CustomerServiceItemDAL_Const.SQL_UPDATE_CUSTOMERSERVICEITEM;
            DbCommand cmd = DB.GetSqlStringCommand(strSQL);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERSERVICEITEMID, DbType.Int32, csi.CUSTOMERSERVICEITEMID);
            AddInSqlParameters(ref cmd, csi);
            DB.ExecuteNonQuery(cmd);
        }

        public void DeleteCustomerServiceItem(int iCustomerServiceItemID)
        {
            string strSQL = CustomerServiceItemDAL_Const.SQL_DELETE_CUSTOMERSERVICEITEM;
            DbCommand cmd = DB.GetSqlStringCommand(strSQL);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERSERVICEITEMID, DbType.Int32, iCustomerServiceItemID);
            DB.ExecuteNonQuery(cmd);
        }

        public DataSet QueryCustomerServiceItem(string sFilter)
        {
            string strSQL = CustomerServiceItemDAL_Const.SQL_SELECT_CUSTOMERSERVICEITEM;
            if (sFilter.Trim() != string.Empty)
                strSQL += " WHERE " + sFilter;

            DbCommand cmd = DB.GetSqlStringCommand(strSQL);
            return DB.ExecuteDataSet(cmd);
        }

        public void UpdateLastServiceTime(int iCustomerServiceItemID, DateTime lastServiceTime)
        {
            string strSQL = CustomerServiceItemDAL_Const.SQL_UPDATE_LASTSERVICETIME;
            DbCommand cmd = DB.GetSqlStringCommand(strSQL);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERSERVICEITEMID, DbType.Int32, iCustomerServiceItemID);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CLASTSERVICETIME, DbType.DateTime, lastServiceTime);
            DB.ExecuteNonQuery(cmd);
        }
         /// <summary>
        /// 获取下次的预约间隔天数
        /// </summary>
        /// <param name="customerServiceItemID"></param>
        /// <returns></returns>
        public int QueryComputerNextResveration(int customerServiceItemID)
        {
            DbCommand cmd = DB.GetSqlStringCommand(CustomerServiceItemDAL_Const.SQL_QUERY_COMPUTER_NEXTRESERVATIONDAY);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERSERVICEITEMID, DbType.Int16, customerServiceItemID);
            DataSet dsCDays = DB.ExecuteDataSet(cmd);
            if (dsCDays.Tables[0].Rows.Count == 0)
            {
                cmd = DB.GetSqlStringCommand(CustomerServiceItemDAL_Const.SQL_QUERY_COMPUTER_PRIORRESERVATIONDAY);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERSERVICEITEMID, DbType.Int16, customerServiceItemID);
                dsCDays = DB.ExecuteDataSet(cmd);
            }
            if (dsCDays.Tables[0].Rows.Count == 0)
            {
                throw new Exception("当前会员的预约项目没有引用标准周期，请设置预约项目的引用周期。");
            }
            return Convert.ToInt16(dsCDays.Tables[0].Rows[0][DBEntityCustomerServiceItemReservationTime.FieldName.CDAYS]);
        }

        /// <summary>
        /// 会员服务扣减
        /// </summary>
        /// <param name="customServiceItemID">服务项目ID</param>
        /// <param name="minusRemain">扣减次数</param>
        /// <param name="lastServiceTime">服务时间</param>
        /// <param name="cMemo">说明</param>
        /// <param name="employeeID">员工ID</param>
        /// <returns></returns>
        public void CompleteCustomServiceItem(int customServiceItemID, int minusRemain, string lastServiceTime,
            string cMemo, string employeeID, DBEntityCustomerServiceItem customerServiceItem, int reservationID)
        {
            DbTransaction aTracation = (DbTransaction)GetConnection().BeginTransaction();
            try
            {
                DbCommand cmd = DB.GetSqlStringCommand(CustomerServiceItemDAL_Const.SQL_SELECT_CUSTOMERSERVICEITEMCONSUME);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERSERVICEITEMID, DbType.Int16, customServiceItemID);
                DataSet dsCustomServiceItemConsume = DB.ExecuteDataSet(cmd);

                cmd = DB.GetSqlStringCommand(CustomerServiceItemDAL_Const.SQL_UPDATE_COMPLETE_CUSTOMERSERVICEITEM);
                if (dsCustomServiceItemConsume.Tables[0].Rows.Count == 0)
                {
                    //第一次服务
                    cmd = DB.GetSqlStringCommand(CustomerServiceItemDAL_Const.SQL_UPDATE_COMPLETE_CUSTOMERSERVICEITEM_FIRSTSERVICE);
                    int serviceDate = GetServiceAvailableDay(customerServiceItem.SERVICEITEMID);
                    //默认永久有效
                    DateTime dtServiceEndTime = new DateTime(3000, 1, 1);
                    if (serviceDate != -1)
                    {
                        //计算服务有效期
                        dtServiceEndTime = Convert.ToDateTime(lastServiceTime).AddDays(serviceDate);
                    }
                    DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CSERVICEENDTIME, DbType.DateTime, dtServiceEndTime);
                }
                string state = DBEntityCustomerServiceItem.ENUM_CSTATUS.C0A;
                if (customerServiceItem.CREMAINCOUNT - minusRemain <= 0)
                {
                    state = DBEntityCustomerServiceItem.ENUM_CSTATUS.C0E;
                }
                DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERSERVICEITEMID, DbType.Int16, customServiceItemID);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CSTATUS, DbType.String, state);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CREMAINCOUNT, DbType.Int16, minusRemain);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CMEMO, DbType.String, cMemo);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CLASTSERVICETIME, DbType.DateTime, Convert.ToDateTime(lastServiceTime));
                DB.ExecuteNonQuery(cmd, aTracation);

                //插入明细消费记录
                cmd = DB.GetSqlStringCommand(CustomerServiceItemDAL_Const.SQL_INSERT_COMPLETE_CUSTOMERSERVICEITEM_ITEMCONSUME);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItemConsume.FieldName.CUSTOMERSERVICEITEMID, DbType.Int16, customServiceItemID);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItemConsume.FieldName.CONSUMETIME, DbType.DateTime, Convert.ToDateTime(lastServiceTime));
                DB.AddInParameter(cmd, DBEntityCustomerServiceItemConsume.FieldName.CMINUSCOUNT, DbType.Int16, minusRemain);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItemConsume.FieldName.CMEMO, DbType.String, cMemo);
                DB.AddInParameter(cmd, DBEntityCustomerServiceItemConsume.FieldName.EMPLOYEEID, DbType.Int16, Convert.ToInt32(employeeID));
                DB.AddInParameter(cmd, DBEntityCustomerServiceItemConsume.FieldName.RESERVATIONID, DbType.Int16, Convert.ToInt32(reservationID));
                DB.ExecuteNonQuery(cmd, aTracation);
                aTracation.Commit();
                //更新最后一次到店时间
                UpdateLastServiceTime(customServiceItemID, DateTime.Now);
            }
            catch(Exception ex)
            {
                aTracation.Rollback();
                throw ex;
            }

        }
        /// <summary>
        /// 获取卡项目服务有效天数
        /// </summary>
        /// <param name="serviceItemID"></param>
        /// <returns></returns>
        private int GetServiceAvailableDay(int serviceItemID)
        {
            DbCommand cmd = DB.GetSqlStringCommand(ServiceItemDAL_Const.SQL_SELECT_SERVICEITEM_AVAILABLEDAY);
            DB.AddInParameter(cmd, DBEntityServiceItem.FieldName.SERVICEITEMID, DbType.Int16, serviceItemID);
            DataSet dsServiceItem = DB.ExecuteDataSet(cmd);
            return Convert.ToInt16(dsServiceItem.Tables[0].Rows[0][DBEntityServiceItem.FieldName.SAVAILABLEDAYS]);
        }

        /// <summary>
        /// 根据用户疗程项目查询消费记录
        /// </summary>
        /// <param name="customServiceItemID"></param>
        /// <returns></returns>
        public DataSet QueryCustomServiceItemConsume(int customServiceItemID)
        {
            DbCommand cmd = DB.GetSqlStringCommand(CustomerServiceItemDAL_Const.SQL_QUERY_CUSTOMERSERVICEITEMCONSUME);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItemConsume.FieldName.CUSTOMERSERVICEITEMID, DbType.Int16, customServiceItemID);
            return DB.ExecuteDataSet(cmd);
        }

        /// <summary>
        /// 根据用户预约记录查询扣减次数
        /// </summary>
        /// <param name="customServiceItemID"></param>
        /// <returns></returns>
        public DataSet QeuryCustomServiceItemConsumeReservationID(int reservationID)
        {
            DbCommand cmd = DB.GetSqlStringCommand(CustomerServiceItemDAL_Const.SQL_QUERY_CUSTOMERSERVICEITEMCONSUME_RESERVATION);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItemConsume.FieldName.RESERVATIONID, DbType.Int16, reservationID);
            return DB.ExecuteDataSet(cmd);
        }
        public void UpdateAccountMoney(int customerServiceItemID, decimal money)
        {
            string sql = CustomerServiceItemDAL_Const.SQL_UPDATE_ACCOUNT_MONEY;
            DbCommand cmd = DB.GetSqlStringCommand(sql);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CTOTALNEEDPAY, DbType.Decimal, money);
            DB.AddInParameter(cmd, DBEntityCustomerServiceItem.FieldName.CUSTOMERSERVICEITEMID, DbType.Int32, customerServiceItemID);
            DB.ExecuteNonQuery(cmd);
        }
    }
}
